with departure_transfer as (
    select billcode
         , start_code
         , start_name
         , order_source_code
         , order_source_name
         , start_agent_code
         , start_agent_name
         , center_type
         , nextstationcode
         , nextstation
         , send_scantime
         , send_shipment_no
         , planned_departure_time
         , actual_departure_time
         , next_planned_arrival_time
         , packagecode
         , goods_type_code
         , goods_type_name
         , arrival_shipment_no
         , arrival_time
         , all_line_arrival_time
         , last_network_code
         , last_network_name
         , transfer_hours
         , route_send_time
         , all_line_route_send_time
         , is_timely
         , untimely_cause
         , all_line_is_timely
         , all_line_untimely_cause
         , route_lack
         , route_error
         , end_code
         , end_name
         , r_nxt_arrival_time
         , all_line_r_nxt_arrival_time
         , r_lastes_arrival_time
         , all_line_r_lastes_arrival_time
         , stop_type
         , pack_send_time
         , mage_region_code
         , mage_region_name
         , business_attribute
         , r_next_lastes_arrival_time
         , all_r_next_lastes_arrival_time
         , arrival_actual_arrival_time
         , arrival_planned_arrival_time
         , reserved_seconds
         , sign_agent_code
         , sign_agent_name
         , end_center_code
         , end_center_name
         , center_arrvial_time
         , replace(terminal_dispatch_code, ',', '_') as terminal_dispatch_code
         , shipment_arrival_time
         , dt
    from jms_dm.dm_cn_departure_transfer_timely_route_dt
    where dt = '{{ execution_date | cst_ds }}'
),
     waybill_incre as (
         select waybill_no
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -15)
           and dt <= '{{ execution_date | cst_ds }}'
           and express_type_name = '兔优达'
     )
insert
overwrite
table
jms_dm.dm_fr_tyd_departure_transfer_timely_route_detail_dt
partition
(
dt
)
select if(t1.billcode = '', null, t1.billcode)                                   as billcode
     , if(t1.start_code = '', null, t1.start_code)                               as start_code
     , if(t1.start_name = '', null, t1.start_name)                               as start_name
     , if(t1.order_source_code = '', null, t1.order_source_code)                 as order_source_code
     , if(t1.order_source_name = '', null, t1.order_source_name)                 as order_source_name
     , if(t1.start_agent_code = '', null, t1.start_agent_code)                   as start_agent_code
     , if(t1.start_agent_name = '', null, t1.start_agent_name)                   as start_agent_name
     , if(t1.center_type = '', null, t1.center_type)                             as center_type
     , if(t1.nextstationcode = '', null, t1.nextstationcode)                     as nextstationcode
     , if(t1.nextstation = '', null, t1.nextstation)                             as nextstation
     , if(t1.send_scantime = '', null, t1.send_scantime)                         as send_scantime
     , if(t1.send_shipment_no = '', null, t1.send_shipment_no)                   as send_shipment_no
     , if(t1.planned_departure_time = '', null, t1.planned_departure_time)       as planned_departure_time
     , if(t1.actual_departure_time = '', null, t1.actual_departure_time)         as actual_departure_time
     , if(t1.next_planned_arrival_time = '', null, t1.next_planned_arrival_time) as next_planned_arrival_time
     , if(t1.packagecode = '', null, t1.packagecode)                             as packagecode
     , if(t1.goods_type_code = '', null, t1.goods_type_code)                     as goods_type_code
     , if(t1.goods_type_name = '', null, t1.goods_type_name)                     as goods_type_name
     , if(t1.arrival_shipment_no = '', null, t1.arrival_shipment_no)             as arrival_shipment_no
     , if(t1.arrival_time = '', null, t1.arrival_time)                           as arrival_time
     , if(t1.all_line_arrival_time = '', null, t1.all_line_arrival_time)         as all_line_arrival_time
     , if(t1.last_network_code = '', null, t1.last_network_code)                 as last_network_code
     , if(t1.last_network_name = '', null, t1.last_network_name)                 as last_network_name
     , if(t1.transfer_hours = '', null, t1.transfer_hours)                       as transfer_hours
     , if(t1.route_send_time = '', null, t1.route_send_time)                     as route_send_time
     , if(t1.all_line_route_send_time = '', null, t1.all_line_route_send_time)   as all_line_route_send_time
     , if(t1.is_timely = '', null, t1.is_timely)                                 as is_timely
     , if(t1.untimely_cause = '', null, t1.untimely_cause)                       as untimely_cause
     , if(t1.all_line_is_timely = '', null, t1.all_line_is_timely)               as all_line_is_timely
     , if(t1.all_line_untimely_cause = '', null, t1.all_line_untimely_cause)     as all_line_untimely_cause
     , if(t1.route_lack = '', null, t1.route_lack)                               as route_lack
     , if(t1.route_error = '', null, t1.route_error)                             as route_error
     , if(t1.end_code = '', null, t1.end_code)                                   as end_code
     , if(t1.end_name = '', null, t1.end_name)                                   as end_name
     , if(t1.r_nxt_arrival_time = '', null, t1.r_nxt_arrival_time)               as r_nxt_arrival_time
     , if(t1.all_line_r_nxt_arrival_time = '', null,
          t1.all_line_r_nxt_arrival_time)                                        as all_line_r_nxt_arrival_time
     , if(t1.r_lastes_arrival_time = '', null, t1.r_lastes_arrival_time)         as r_lastes_arrival_time
     , if(t1.all_line_r_lastes_arrival_time = '', null,
          t1.all_line_r_lastes_arrival_time)                                     as all_line_r_lastes_arrival_time
     , if(t1.stop_type = '', null, t1.stop_type)                                 as stop_type
     , if(t1.pack_send_time = '', null, t1.pack_send_time)                       as pack_send_time
     , if(t1.mage_region_code = '', null, t1.mage_region_code)                   as mage_region_code
     , if(t1.mage_region_name = '', null, t1.mage_region_name)                   as mage_region_name
     , if(t1.business_attribute = '', null, t1.business_attribute)               as business_attribute
     , if(t1.r_next_lastes_arrival_time = '', null,
          t1.r_next_lastes_arrival_time)                                         as r_next_lastes_arrival_time
     , if(t1.all_r_next_lastes_arrival_time = '', null,
          t1.all_r_next_lastes_arrival_time)                                     as all_r_next_lastes_arrival_time
     , if(t1.arrival_actual_arrival_time = '', null,
          t1.arrival_actual_arrival_time)                                        as arrival_actual_arrival_time
     , if(t1.arrival_planned_arrival_time = '', null,
          t1.arrival_planned_arrival_time)                                       as arrival_planned_arrival_time
     , if(t1.reserved_seconds = '', null, t1.reserved_seconds)                   as reserved_seconds
     , if(t1.sign_agent_code = '', null, t1.sign_agent_code)                     as sign_agent_code
     , if(t1.sign_agent_name = '', null, t1.sign_agent_name)                     as sign_agent_name
     , if(t1.end_center_code = '', null, t1.end_center_code)                     as end_center_code
     , if(t1.end_center_name = '', null, t1.end_center_name)                     as end_center_name
     , if(t1.center_arrvial_time = '', null, t1.center_arrvial_time)             as center_arrvial_time
     , if(t1.terminal_dispatch_code = '', null, t1.terminal_dispatch_code)       as terminal_dispatch_code
     , if(t1.shipment_arrival_time = '', null, t1.shipment_arrival_time)         as shipment_arrival_time
     , t1.dt                                                                     as date_time
     , t1.dt
from departure_transfer t1
         join waybill_incre t2
              on t1.billcode = t2.waybill_no
;